package com.aier.admin.service.impl;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.List;
import java.util.Map;

import javax.annotation.Resource;

import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Component;

import com.aier.admin.entity.BuZou;
import com.aier.admin.entity.CaiPu;
import com.aier.admin.entity.CaiXi;
import com.aier.admin.entity.FuLiao;
import com.aier.admin.entity.GongYi;
import com.aier.admin.entity.KouWei;
import com.aier.admin.entity.NanDu;
import com.aier.admin.entity.ZhuLiao;
import com.aier.admin.entity.mapper.BuZouRowMapper;
import com.aier.admin.entity.mapper.CaiPuRowMapper;
import com.aier.admin.entity.mapper.CaiXiRowMapper;
import com.aier.admin.entity.mapper.FuLiaoRowMapper;
import com.aier.admin.entity.mapper.GongYiRowMapper;
import com.aier.admin.entity.mapper.KouWeiRowMapper;
import com.aier.admin.entity.mapper.NanDuRowMapper;
import com.aier.admin.entity.mapper.ZhuLiaoRowMapper;
import com.aier.admin.service.IAdminCaiPuService;
import com.aier.common.dao.ICommonDAO;
import com.aier.user.entity.userapp.Advert;
import com.aier.user.entity.userapp.AdvertRowMapper;
import com.aier.user.entity.userapp.CaiPuComment;
import com.aier.user.entity.userapp.CaiPuCommentRowMapper;

@Component("adminCaiPuService")
public class AdminCaiPuServiceImpl_J implements IAdminCaiPuService{
    @Resource(name="commonDao")
    private ICommonDAO commonDao;
    
    public void setCommonDao(ICommonDAO commonDao) {
        this.commonDao = commonDao;
    }

    @Override
    public int insertCaipu(String cp_name,String cp_desc,String cp_time,String cp_ptime,String cp_number,
            long cx_id,long nd_id,long gy_id,long kw_id,String cp_img,
            String zhuliaos,String fuliaos,String buzous) {
        String[] zhuliaoList = zhuliaos.split("@");
        String[] fuliaoList = fuliaos.split("@");
        String[] buzouList = buzous.split("@");
        
        String zl_name = null;
        String fl_name = null;
        String flg_name = null;
        String bz_msg = null;
        String bz_img = null;
        
        // 添加新的菜谱cp_name(判断),
        int cpnum = commonDao.countByQuali("from userapp_caipu where cp_name=?", new Object[] { cp_name});
        if(cpnum==0){
            commonDao.update("insert into userapp_caipu(cp_name,cp_desc,cp_time,cp_ptime,cp_number,cx_id,nd_id,gy_id,kw_id,cp_img) " +
                    "values(?,?,?,?,?,?,?,?,?,?) ",
                new Object[] { cp_name,cp_desc,cp_time,cp_ptime,cp_number,cx_id,nd_id,gy_id,kw_id,cp_img });
        }else{
            throw new RuntimeException("菜单名称:" + cp_name + "已经存在");
        }
        
        //菜谱id
        long cp_id = (long) commonDao.listByObject("select cp_id from userapp_caipu where cp_name=?",
                new Object[] { cp_name},new RowMapper<Long>() {
                    @Override
                    public Long mapRow(ResultSet rs, int arg1) throws SQLException {
                        return rs.getLong("cp_id");
                    } 
                });
        
        // 添加新菜单步奏
        for(String buzou:buzouList){
            if(!buzou.trim().equals("")){
                String[] tmpList = buzou.split("#");
                bz_msg = tmpList[0];
                //步骤操作:添加到步奏表;
                commonDao.update("insert into userapp_buzou(bz_msg,cp_id) values(?,?) ",
                        new Object[] { bz_msg,cp_id });
                for(int i=1;i<tmpList.length;i++){
                    bz_img = tmpList[i];
                    if(!"".equals(bz_img)){
                        commonDao.update("insert into userapp_buzou_img(bz_id,bz_img) values" +
                                "((select bz_id from userapp_buzou where bz_msg=? and cp_id=?),?) ",
                                new Object[] { bz_msg,cp_id,bz_img });                        
                    }
                }
            }
        }
        
        for(String zhuliao:zhuliaoList){
            if(!zhuliao.trim().equals("")){
                zl_name = zhuliao.split("#")[0];
                flg_name = zhuliao.split("#")[1];
                //主料操作:1是新主料,则添加到主料表;2.是新的份量词汇,则添加到份量表;3.添加菜谱和主料的关系表(含份量)
                int zl_num = commonDao.countByQuali("from userapp_zhuliao where zl_name=?", new Object[] { zl_name});
                int flg_num = commonDao.countByQuali("from userapp_fengliang where flg_name=?", new Object[] { flg_name});
                if(zl_num==0){
                    commonDao.update("insert into userapp_zhuliao(zl_name) values(?) ",new Object[] { zl_name });                    
                }
                if(flg_num==0){
                    commonDao.update("insert into userapp_fengliang(flg_name) values(?) ",new Object[] { flg_name });    
                }
                long zl_id = (long) commonDao.listByObject("select zl_id from userapp_zhuliao where zl_name=?",
                        new Object[] { zl_name},new RowMapper<Long>() {
                            @Override
                            public Long mapRow(ResultSet rs, int arg1) throws SQLException {
                                return rs.getLong("zl_id");
                            } 
                        });
                long flg_id = (long) commonDao.listByObject("select flg_id from userapp_fengliang where flg_name=?",
                        new Object[] { flg_name},new RowMapper<Long>() {
                            @Override
                            public Long mapRow(ResultSet rs, int arg1) throws SQLException {
                                return rs.getLong("flg_id");
                            }
                        });
                commonDao.update("insert into userapp_caipu_zhuliao(cp_id,zl_id,flg_id) values(?,?,?) ",new Object[] { cp_id,zl_id,flg_id });
                
            }
        }

        for(String fuliao:fuliaoList){
            if(!fuliao.trim().equals("")){
                fl_name = fuliao.split("#")[0];
                flg_name = fuliao.split("#")[1];
                //辅料操作:1是新辅料,则添加到辅料表;2.是新的份量词汇,则添加到份量表;3.添加菜谱和辅料的关系表(含份量)
                int fl_num = commonDao.countByQuali("from userapp_fuliao where fl_name=?", new Object[] { fl_name});
                int flg_num = commonDao.countByQuali("from userapp_fengliang where flg_name=?", new Object[] { flg_name});
                if(fl_num==0){
                    commonDao.update("insert into userapp_fuliao(fl_name) values(?) ",new Object[] { fl_name });                    
                }
                if(flg_num==0){
                    commonDao.update("insert into userapp_fengliang(flg_name) values(?) ",new Object[] { flg_name });    
                }
                long fl_id = (long) commonDao.listByObject("select fl_id from userapp_fuliao where fl_name=?",
                        new Object[] { fl_name},new RowMapper<Long>() {
                            @Override
                            public Long mapRow(ResultSet rs, int arg1) throws SQLException {
                                return rs.getLong("fl_id");
                            } 
                        });
                long flg_id = (long) commonDao.listByObject("select flg_id from userapp_fengliang where flg_name=?",
                        new Object[] { flg_name},new RowMapper<Long>() {
                            @Override
                            public Long mapRow(ResultSet rs, int arg1) throws SQLException {
                                return rs.getLong("flg_id");
                            } 
                        });
                commonDao.update("insert into userapp_caipu_fuliao(cp_id,fl_id,flg_id) values(?,?,?) ",new Object[] { cp_id,fl_id,flg_id });
                                
            }
        }
        return 1;
    }

    @Override
    public Map<String, Object> searchCaiPuListByCondition(int page,
            int pagesize, int sortable, String cp_name, String cp_time,
            long cx_id, long nd_id, long gy_id, long kw_id, List<Long> zl_ids) {
        String sql = " from userapp_caipu as cp where 1=1 ";
        if (null != cp_name&&!"".equals(cp_name)){
            sql += " and cp.cp_name='" + cp_name + "'";
        }
//        if (0 < cp_time){
//            sql += " and cp.cp_time<=" + cp_time;
//        }
        if (-1 != cx_id){
            sql += " and cp.cx_id=" + cx_id;
        }
        if (-1 != nd_id){
            sql += " and cp.nd_id=" + nd_id;
        }
        if (-1 != gy_id){
            sql += " and cp.gy_id=" + gy_id;
        }
        if (-1 != kw_id){
            sql += " and cp.kw_id=" + kw_id;
        }

        if (zl_ids.size()>0){
            String temp_zlids = "(";
            for(int i=0;i<zl_ids.size();i++){
                if(i==zl_ids.size()-1){
                    temp_zlids = temp_zlids + zl_ids.get(i) + ")";
                }else{
                    temp_zlids = temp_zlids + zl_ids.get(i) + ",";
                }
            }
            sql += " and cp.cp_id in (select DISTINCT(cp_id) from userapp_caipu_zhuliao where zl_id in " + temp_zlids + ")";
        }
        String sql_first = sql;
        switch (sortable) {
            case CAIPU_SORTABLE_TIME:
                sql += " order by cp.cp_time";
                break;
            case CAIPU_SORTABLE_TIME_DESC:
                sql += " order by cp.cp_time desc";
                break;
            default:
                sql += " order by cp.cp_id desc";
                break;
        }
        return commonDao
                .queryPage(
                        "select cp.*,(select cx_name from userapp_caixi where cx_id = cp.cx_id) AS cx_name," 
                      + "(select nd_name from userapp_nandu where nd_id = cp.nd_id) AS nd_name,"
                      + "(select gy_name from userapp_gongyi where gy_id = cp.gy_id) AS gy_name,"
                      + "(select kw_name from userapp_kouwei where kw_id = cp.kw_id) AS kw_name,"
                      + "(select group_concat(concat((select zl_name from userapp_zhuliao where zl_id=cp_zl.zl_id)," +
                      "(select flg_name from userapp_fengliang where flg_id=cp_zl.flg_id))) " +
                      "from userapp_caipu_zhuliao cp_zl where cp_id=cp.cp_id) as zl_names,"
                      + "(select group_concat(concat((select fl_name from userapp_fuliao where fl_id=cp_fl.fl_id)," +
                      "(select flg_name from userapp_fengliang where flg_id=cp_fl.flg_id))) " +
                      "from userapp_caipu_fuliao cp_fl where cp_id=cp.cp_id) as fl_names "
                      + sql, "select count(*) " + sql_first, page,
                        pagesize, new Object[] {  }, new Object[] {  },
                        new CaiPuRowMapper(CaiPuRowMapper.CAIPU_AND_STR));   
    }

    @SuppressWarnings("unchecked")
    @Override
    public List<GongYi> searchGongYiList() {
        return (List<GongYi>) commonDao.list("select * from userapp_gongyi", new GongYiRowMapper());
    }

    @Override
    public Map<String, Object> searchGongYiList(int page, int pagesize) {
        String sql = " from userapp_gongyi gy";
        return commonDao.queryPage("select *,(select COUNT(cp_id) from userapp_caipu where gy_id=gy.gy_id) gy_num " + sql,
                "select count(*) " + sql,page,pagesize,new Object[] {  }, new Object[] {  }, new GongYiRowMapper(GongYiRowMapper.GONGYI_AND_NUM));
    }

    @SuppressWarnings("unchecked")
    @Override
    public List<KouWei> searchKouWeiList() {
        return (List<KouWei>) commonDao.list("select * from userapp_kouwei", new KouWeiRowMapper());
    }
    
    @Override
    public Map<String, Object> searchKouWeiList(int page, int pagesize) {
        String sql = " from userapp_kouwei kw";
        return commonDao.queryPage("select *,(select COUNT(cp_id) from userapp_caipu where kw_id=kw.kw_id) kw_num  " + sql,
                "select count(*) " + sql,page,pagesize,new Object[] {  }, new Object[] {  }, new KouWeiRowMapper(KouWeiRowMapper.KOUWEI_AND_NUM));
    }
    
    @SuppressWarnings("unchecked")
    @Override
    public List<NanDu> searchNanDuList() {
        return (List<NanDu>) commonDao.list("select * from userapp_nandu", new NanDuRowMapper());
    }
    
    @Override
    public Map<String, Object> searchNanDuList(int page, int pagesize) {
        String sql = " from userapp_nandu nd";
        return commonDao.queryPage("select *,(select COUNT(cp_id) from userapp_caipu where nd_id=nd.nd_id) nd_num  " + sql,
                "select count(*) " + sql,page,pagesize,new Object[] {  }, new Object[] {  }, new NanDuRowMapper(NanDuRowMapper.NANDU_AND_NUM));
    }
    
    @SuppressWarnings("unchecked")
    @Override
    public List<CaiXi> searchCaiXiList() {
        return (List<CaiXi>) commonDao.list("select * from userapp_caixi", new CaiXiRowMapper());
    }
    
    @Override
    public Map<String, Object> searchCaiXiList(int page, int pagesize) {
        String sql = " from userapp_caixi cx";
        return commonDao.queryPage("select *,(select COUNT(cp_id) from userapp_caipu where cx_id=cx.cx_id) cx_num   " + sql,
                "select count(*) " + sql,page,pagesize,new Object[] {  }, new Object[] {  }, new CaiXiRowMapper(CaiXiRowMapper.CAIXI_AND_NUM));
    }
    
    @Override
    public Map<String, Object> searchZhuLiaoList(int page, int pagesize){
        String sql = " from userapp_zhuliao zl";
        return commonDao.queryPage("select * " + sql,
                "select count(*) " + sql,page,pagesize,new Object[] {  }, new Object[] {  }, new ZhuLiaoRowMapper());
    }
    
    @Override
    public int deleteCaiPu(long cp_id_del) {
        if(commonDao.update("delete from userapp_caipu where cp_id=? ",
                new Object[] { cp_id_del})>0){
            return 1;
        }else{
            return 0;
        }
    }

    @Override
    public CaiPu searchCaiPuById(long cp_id_del) {
        CaiPu caipu = new CaiPu();
        caipu = (CaiPu) commonDao.listByObject("select cp.*," +
                "(select group_concat(concat((select zl_name from userapp_zhuliao where zl_id=cp_zl.zl_id),'#'," +
                "(select flg_name from userapp_fengliang where flg_id=cp_zl.flg_id))) " +
                "from userapp_caipu_zhuliao cp_zl where cp_id=cp.cp_id) as zl_names," +
                "(select group_concat(concat((select fl_name from userapp_fuliao where fl_id=cp_fl.fl_id),'#'," +
                "(select flg_name from userapp_fengliang where flg_id=cp_fl.flg_id))) " +
                "from userapp_caipu_fuliao cp_fl where cp_id=cp.cp_id) as fl_names " +
                " from userapp_caipu cp where cp.cp_id=?", new Object[] { cp_id_del}, new CaiPuRowMapper(CaiPuRowMapper.CAIPU_AND_UPDATE));
        caipu.setBuZouList(searchBuZouListByCpid(cp_id_del));
        
        return caipu;
    }
    
    @SuppressWarnings("unchecked")
    @Override
    public List<BuZou> searchBuZouListByCpid(long cp_id) {
        return (List<BuZou>) commonDao.list("SELECT ub.*,(select group_concat(bz_img) from userapp_buzou_img where bz_id=ub.bz_id) as bz_imgs  " +
                "FROM userapp_buzou ub where ub.cp_id=?", 
                new Object[] { cp_id}, new BuZouRowMapper(BuZouRowMapper.BUZOU_AND_STR));
    }

    @Override
    public int updateCaipu(long cp_id_del, String cp_name, String cp_desc,
            String cp_time,String cp_ptime,String cp_number, long cx_id, long nd_id, long gy_id, long kw_id,
            String cp_img, String zhuliaos, String fuliaos, String buzous) {
        String[] zhuliaoList = zhuliaos.split("@");
        String[] fuliaoList = fuliaos.split("@");
        String[] buzouList = buzous.split("@");
        
        String zl_name = null;
        String fl_name = null;
        String flg_name = null;
        String bz_msg = null;
        String bz_img = null;
        
        try {
            commonDao.update("delete from userapp_caipu where cp_id=?",
                    new Object[] { cp_id_del});
            // 添加菜谱基本信息
            commonDao.update("insert into userapp_caipu(cp_id,cp_name,cp_desc,cp_time,cp_ptime,cp_number,cx_id,nd_id,gy_id,kw_id,cp_img) " +
                    "values(?,?,?,?,?,?,?,?,?,?,?) ",
                new Object[] { cp_id_del,cp_name,cp_desc,cp_time,cp_ptime,cp_number,cx_id,nd_id,gy_id,kw_id,cp_img });
            
            // 添加新菜单步奏
            for(String buzou:buzouList){
                if(!buzou.trim().equals("")){
                    String[] tmpList = buzou.split("#");
                    bz_msg = tmpList[0];
                    //步骤操作:添加到步奏表;
                    commonDao.update("insert into userapp_buzou(bz_msg,cp_id) values(?,?) ",
                            new Object[] { bz_msg,cp_id_del });
                    for(int i=1;i<tmpList.length;i++){
                        bz_img = tmpList[i];
                        if(!"".equals(bz_img)){
                            commonDao.update("insert into userapp_buzou_img(bz_id,bz_img) " +
                                    "values((select bz_id from userapp_buzou where bz_msg=? and cp_id=?),?) ",
                                    new Object[] { bz_msg,cp_id_del,bz_img });                        
                        }
                    }
                }
            }
            
            for(String zhuliao:zhuliaoList){
                if(!zhuliao.trim().equals("")){
                    zl_name = zhuliao.split("#")[0];
                    flg_name = zhuliao.split("#")[1];
                    //主料操作:1是新主料,则添加到主料表;2.是新的份量词汇,则添加到份量表;3.添加菜谱和主料的关系表(含份量)
                    int zl_num = commonDao.countByQuali("from userapp_zhuliao where zl_name=?", new Object[] { zl_name});
                    int flg_num = commonDao.countByQuali("from userapp_fengliang where flg_name=?", new Object[] { flg_name});
                    if(zl_num==0){
                        commonDao.update("insert into userapp_zhuliao(zl_name) values(?) ",new Object[] { zl_name });                    
                    }
                    if(flg_num==0){
                        commonDao.update("insert into userapp_fengliang(flg_name) values(?) ",new Object[] { flg_name });    
                    }
                    long zl_id = (long) commonDao.listByObject("select zl_id from userapp_zhuliao where zl_name=?",
                            new Object[] { zl_name},new RowMapper<Long>() {
                                @Override
                                public Long mapRow(ResultSet rs, int arg1) throws SQLException {
                                    return rs.getLong("zl_id");
                                } 
                            });
                    long flg_id = (long) commonDao.listByObject("select flg_id from userapp_fengliang where flg_name=?",
                            new Object[] { flg_name},new RowMapper<Long>() {
                                @Override
                                public Long mapRow(ResultSet rs, int arg1) throws SQLException {
                                    return rs.getLong("flg_id");
                                } 
                            });
                    commonDao.update("insert into userapp_caipu_zhuliao(cp_id,zl_id,flg_id) values(?,?,?) ",new Object[] { cp_id_del,zl_id,flg_id });
                    
                }
            }

            for(String fuliao:fuliaoList){
                if(!fuliao.trim().equals("")){
                    fl_name = fuliao.split("#")[0];
                    flg_name = fuliao.split("#")[1];
                    //辅料操作:1是新辅料,则添加到辅料表;2.是新的份量词汇,则添加到份量表;3.添加菜谱和辅料的关系表(含份量)
                    int fl_num = commonDao.countByQuali("from userapp_fuliao where fl_name=?", new Object[] { fl_name});
                    int flg_num = commonDao.countByQuali("from userapp_fengliang where flg_name=?", new Object[] { flg_name});
                    if(fl_num==0){
                        commonDao.update("insert into userapp_fuliao(fl_name) values(?) ",new Object[] { fl_name });                    
                    }
                    if(flg_num==0){
                        commonDao.update("insert into userapp_fengliang(flg_name) values(?) ",new Object[] { flg_name });    
                    }
                    long fl_id = (long) commonDao.listByObject("select fl_id from userapp_fuliao where fl_name=?",
                            new Object[] { fl_name},new RowMapper<Long>() {
                                @Override
                                public Long mapRow(ResultSet rs, int arg1) throws SQLException {
                                    return rs.getLong("fl_id");
                                }
                            });
                    long flg_id = (long) commonDao.listByObject("select flg_id from userapp_fengliang where flg_name=?",
                            new Object[] { flg_name},new RowMapper<Long>() {
                                @Override
                                public Long mapRow(ResultSet rs, int arg1) throws SQLException {
                                    return rs.getLong("flg_id");
                                } 
                            });
                    commonDao.update("insert into userapp_caipu_fuliao(cp_id,fl_id,flg_id) values(?,?,?) ",new Object[] { cp_id_del,fl_id,flg_id });
                                    
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
            return 0;
        }

        return 1;
    }

    @Override
    public GongYi searchGongYiByName(String gy_name_add) {
        return (GongYi) commonDao.listByObject("select * from userapp_gongyi where gy_name=?", new Object[]{gy_name_add}, new GongYiRowMapper());
    }

    @Override
    public NanDu searchNanDuByName(String nd_name_add) {
        return (NanDu) commonDao.listByObject("select * from userapp_nandu where nd_name=?", new Object[]{nd_name_add}, new NanDuRowMapper());
    }

    @Override
    public CaiXi searchCaiXiByName(String cx_name_add) {
        return (CaiXi) commonDao.listByObject("select * from userapp_caixi where cx_name=?", new Object[]{cx_name_add}, new CaiXiRowMapper());
    }

    @Override
    public KouWei searchKouWeiByName(String kw_name_add) {
        return (KouWei) commonDao.listByObject("select * from userapp_kouwei where kw_name=?", new Object[]{kw_name_add}, new KouWeiRowMapper());
    }

    @Override
    public ZhuLiao searchZhuLiaoById(long zl_id){
        return (ZhuLiao) commonDao.listByObject("select * from userapp_zhuliao where zl_id=?", new Object[]{zl_id}, new ZhuLiaoRowMapper());    
    }
    
    @Override
    public int insertGongYi(String gy_name_add) {
        if(commonDao.update("insert into userapp_gongyi(gy_name) values(?) ",new Object[] {gy_name_add})>0){
            return 1;
        }else{
            return 0;
        }
    }

    @Override
    public int insertNanDu(String nd_name_add) {
        if(commonDao.update("insert into userapp_nandu(nd_name) values(?) ",new Object[] {nd_name_add})>0){
            return 1;
        }else{
            return 0;
        }
    }

    @Override
    public int insertCaiXi(String cx_name_add) {
        if(commonDao.update("insert into userapp_caixi(cx_name) values(?) ",new Object[] {cx_name_add})>0){
            return 1;
        }else{
            return 0;
        }
    }

    @Override
    public int insertKouWei(String kw_name_add) {
        if(commonDao.update("insert into userapp_kouwei(kw_name) values(?) ",new Object[] {kw_name_add})>0){
            return 1;
        }else{
            return 0;
        }
    }

    @Override
    public int deleteGongYi(long gy_id_del) {
        if(commonDao.update("delete from userapp_gongyi where gy_id=? ",
                new Object[] { gy_id_del})>0){
            return 1;
        }else{
            return 0;
        }
    }

    @Override
    public int deleteNanDu(long nd_id_del) {
        if(commonDao.update("delete from userapp_nandu where nd_id=? ",
                new Object[] { nd_id_del})>0){
            return 1;
        }else{
            return 0;
        }
    }

    @Override
    public int deleteCaiXi(long cx_id_del) {
        if(commonDao.update("delete from userapp_caixi where cx_id=? ",
                new Object[] { cx_id_del})>0){
            return 1;
        }else{
            return 0;
        }
    }

    @Override
    public int deleteKouWei(long kw_id_del) {
        if(commonDao.update("delete from userapp_kouwei where kw_id=? ",
                new Object[] { kw_id_del})>0){
            return 1;
        }else{
            return 0;
        }
    }

    @Override
    public int updateZhuLiao(long zl_id, String zl_img){
        if(commonDao.update("update userapp_zhuliao set zl_img=? where zl_id=? ",
                new Object[] { zl_img,zl_id})>0){
            return 1;
        }else{
            return 0;
        }        
    }

    @Override
    public int updateZhuLiao(String zl_name, String zl_img){
        if(commonDao.update("update userapp_zhuliao set zl_img=? where zl_name=? ",
                new Object[] { zl_img,zl_name})>0){
            return 1;
        }else{
            return 0;
        }        
    }
    
    @SuppressWarnings("unchecked")
    @Override
    public List<CaiPu> searchCaiPuListByCxid(long cx_id){
        String sql = " from userapp_caipu as cp where 1=1 ";
        if (-1 != cx_id){
            sql += " and cp.cx_id=" + cx_id;
        }
        
        return (List<CaiPu>) commonDao.list(
                        "select cp.*,(select nd_name from userapp_nandu where nd_id = cp.nd_id) AS nd_name,"
                      + "(select gy_name from userapp_gongyi where gy_id = cp.gy_id) AS gy_name,"
                      + "(select kw_name from userapp_kouwei where kw_id = cp.kw_id) AS kw_name "
                      + sql, new Object[] {  },
                        new CaiPuRowMapper(CaiPuRowMapper.CAIPU_AND_STR_APP)); 
    }
    
    @Override
    public Map<String, Object> searchCaiPuListByCxid(int page, int pagesize,long cx_id) {
        String sql = " from userapp_caipu as cp where 1=1 ";
        if (-1 != cx_id){
            sql += " and cp.cx_id=" + cx_id;
        }
        return commonDao.queryPage("select cp.*,(select nd_name from userapp_nandu where nd_id = cp.nd_id) AS nd_name,"
                + "(select gy_name from userapp_gongyi where gy_id = cp.gy_id) AS gy_name,"
                + "(select kw_name from userapp_kouwei where kw_id = cp.kw_id) AS kw_name "
                + sql,page, pagesize, new Object[] {},new CaiPuRowMapper(CaiPuRowMapper.CAIPU_AND_STR_APP));
    }
    
    @Override
    public CaiPu searchCaiPuById_APP(long cp_id){
        CaiPu caipu = new CaiPu();
        caipu = (CaiPu) commonDao.listByObject("select cp.*,(select nd_name from userapp_nandu where nd_id = cp.nd_id) AS nd_name," +
                "(select gy_name from userapp_gongyi where gy_id = cp.gy_id) AS gy_name," + 
                "(select kw_name from userapp_kouwei where kw_id = cp.kw_id) AS kw_name " +
                " from userapp_caipu cp where cp.cp_id=?", new Object[] { cp_id}, new CaiPuRowMapper(CaiPuRowMapper.CAIPU_AND_STR_APP));
        caipu.setBuZouList(searchBuZouListByCpid(cp_id));
        caipu.setZhuLiaoList(searchZhuLiaoListByCpid(cp_id));
        caipu.setFuLiaoList(searchFuLiaoListByCpid(cp_id));
        return caipu;        
    }

    @SuppressWarnings("unchecked")
    private List<FuLiao> searchFuLiaoListByCpid(long cp_id) {
        return (List<FuLiao>) commonDao.list(
                "select ufl.*,uffl.flg_name from userapp_caipu_fuliao ucf LEFT JOIN userapp_fuliao ufl on ucf.fl_id=ufl.fl_id " + 
                "LEFT JOIN userapp_fengliang uffl on ucf.flg_id=uffl.flg_id where ucf.cp_id=?",
                new Object[] { cp_id}, new FuLiaoRowMapper(FuLiaoRowMapper.CAIPU_AND_FLG));
    }
    
    @SuppressWarnings("unchecked")
    private List<ZhuLiao> searchZhuLiaoListByCpid(long cp_id) {
        return (List<ZhuLiao>) commonDao.list(
                "select uzl.*,ufl.flg_name from userapp_caipu_zhuliao ucz LEFT JOIN userapp_zhuliao uzl on ucz.zl_id=uzl.zl_id " + 
                "LEFT JOIN userapp_fengliang ufl on ucz.flg_id=ufl.flg_id where ucz.cp_id=?",
                new Object[] { cp_id}, new ZhuLiaoRowMapper(ZhuLiaoRowMapper.CAIPU_AND_FLG));
    }

    @SuppressWarnings("unchecked")
    @Override
    public List<CaiPu> searchFavoriteCaiPuList(Long uid, long mid) {
        String sql = " from userapp_caipu as cp where cp_id in (select uf.contentid from userapp_favorite uf where uf.uid=? and uf.mid=?) ";
        
        return (List<CaiPu>) commonDao.list(
                        "select cp.*,(select nd_name from userapp_nandu where nd_id = cp.nd_id) AS nd_name,"
                      + "(select gy_name from userapp_gongyi where gy_id = cp.gy_id) AS gy_name,"
                      + "(select kw_name from userapp_kouwei where kw_id = cp.kw_id) AS kw_name,"
                      + "(select uf.ufid from userapp_favorite uf where uf.uid=? and uf.mid=? and uf.contentid=cp.cp_id) AS uf_id "
                      + sql, new Object[] {uid, mid,uid,mid },
                        new CaiPuRowMapper(CaiPuRowMapper.CAIPU_AND_FAVORITE_APP)); 
    }
    
    @Override
    public Map<String, Object> searchFavoriteCaiPuList(int page, int pagesize,Long uid, long mid) {
        
        return commonDao.queryPage("select cp.*,(select nd_name from userapp_nandu where nd_id = cp.nd_id) AS nd_name,"
                + "(select gy_name from userapp_gongyi where gy_id = cp.gy_id) AS gy_name,"
                + "(select kw_name from userapp_kouwei where kw_id = cp.kw_id) AS kw_name,"
                + " uf.ufid AS uf_id from userapp_caipu cp left JOIN userapp_favorite uf ON uf.contentid = cp.cp_id WHERE uf.uid =? AND uf.mid =?",
                page, pagesize, new Object[] {uid, mid },new CaiPuRowMapper(CaiPuRowMapper.CAIPU_AND_FAVORITE_APP));
    }

    @Override
    public int insertFavoriteCaiPu(Long uid, long mid, long cp_id, String ageInfo) {
        int flg_num = commonDao.countByQuali("from userapp_favorite where uid=? and mid=? and contentid=?", new Object[] { uid,mid,cp_id});
        int s = 1;
        if(flg_num == 0){
            if(commonDao.update("insert into userapp_favorite(uid,mid,contentid,description) values(?,?,?,?) ",
                    new Object[] {uid,mid,cp_id,ageInfo})<=0){
                s = 0;
            }        
        }
        return s;
    }

    @Override
    public int deleteFavoriteCaiPu(long uf_id) {
        if(commonDao.update("delete from userapp_favorite where ufid=? ",
                new Object[] { uf_id})>0){
            return 1;
        }else{
            return 0;
        }
    }

    @SuppressWarnings("unchecked")
    @Override
    public List<CaiPuComment> searchCaiPuCommentList(long cp_id) {
        return (List<CaiPuComment>) commonDao.list("select ucc.*,(select realname from user_info where uid=ucc.uid) as realname " +
                "from userapp_caipu_comment ucc where ucc.cp_id=?", 
                new Object[]{cp_id}, new CaiPuCommentRowMapper(CaiPuCommentRowMapper.AND_USERNAME));
    }
    
    @Override
    public Map<String, Object> searchCaiPuCommentList(int page, int pagesize,long cp_id) {
        String sql = "select ucc.*,(select realname from user_info where uid=ucc.uid) as realname " +
                "from userapp_caipu_comment ucc where ucc.cp_id=?";
        return commonDao.queryPage(sql,page, pagesize, new Object[] {cp_id},new CaiPuCommentRowMapper(CaiPuCommentRowMapper.AND_USERNAME));
    }

    @Override
    public int insertCaiPuComment(Long uid, long cp_id, String comment,
            Timestamp cdate) {
        int s = 1;
        if(commonDao.update("insert into userapp_caipu_comment(cp_id,uid,comment,cdate) values(?,?,?,?) ",
                new Object[] {cp_id,uid,comment,cdate})<=0){
            s = 0;
        }        
        return s;
    }

    @SuppressWarnings("unchecked")
    @Override
    public List<String> searchTest() {
        return (List<String>) commonDao.list(
                "SELECT cs.headimg AS img FROM class_stubook cs UNION SELECT c.image AS img FROM country c UNION SELECT df.headimg AS img " +
                "FROM dailyfood df UNION SELECT h.picurl AS img FROM homework h UNION SELECT hu.picurl FROM homework_upload hu " +
                "UNION SELECT sb.bimage FROM sys_bulletin sb UNION SELECT sp.image FROM sys_parenting sp " +
                "UNION SELECT tii.img FROM teacher_icard_info tii UNION SELECT uii.img FROM user_icard_info uii " +
                "UNION SELECT ui.headimg FROM user_info ui UNION SELECT ubi.bz_img FROM userapp_buzou_img ubi " +
                "UNION SELECT uz.zl_img FROM userapp_zhuliao uz UNION SELECT uc.cp_img FROM userapp_caipu uc", 
                new Object[] {}, new RowMapper<String>() {
                    @Override
                    public String mapRow(ResultSet rs, int arg1)
                        throws SQLException {
                        return rs.getString("img");
                    }
                });
    }

    @Override
    public long updateCaipu_tmpinfo(String string, String name) {
        if(string.equals("nd")){
            NanDu nd = searchNanDuByName(name);
            if(nd!=null){
                return nd.getNd_id();
            }else{
                insertNanDu(name);
                return searchNanDuByName(name).getNd_id();
            }
        }else if(string.equals("gy")){
            GongYi gy = searchGongYiByName(name);
            if(gy!=null){
                return gy.getGy_id();
            }else{
                insertGongYi(name);
                return searchGongYiByName(name).getGy_id();
            }            
        }else if(string.equals("kw")){
            KouWei kw = searchKouWeiByName(name);
            if(kw!=null){
                return kw.getKw_id();
            }else{
                insertKouWei(name);
                return searchKouWeiByName(name).getKw_id();
            }            
        }else{
            return 0;
        }

    }

    @SuppressWarnings("unchecked")
    @Override
    public List<Advert> searchAdvertList() {
        return (List<Advert>) commonDao.list("select * from userapp_advert", 
                new Object[]{}, new AdvertRowMapper());
    }
}
